package sdfi.lee.DAO;

import java.io.UnsupportedEncodingException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;

import sdfi.lee.CE.CEMap;
import sdfi.lee.Entity.Qanswer;
import sdfi.lee.Entity.Question;
import sdfi.lee.Logger.MyLogger;
import sdfi.lee.Properties.ReadXMLFromString;
import sdfi.lee.Question.Relation;

public class QuestionDAO {
//根据profie的ID属性得到相关联系 	
public List<Relation> getRelation(String profileid)
	{
		List<Relation> list=new ArrayList<Relation>();
		Session session=DAO.openSession();
		String  sql="select id 'id',extractValue(a.answer,'/profile//NE/@name|//NE/profileID') 'answer'" +
				" from answer as a where a.answer like '%ID = \""+profileid+"\" %'";
		try
		{
			
			Connection conn=session.connection();
			ResultSet rs=conn.createStatement().executeQuery(sql);
			conn.commit();
			if(!rs.next()) return list;
			Relation rl=null;
			String restring=rs.getString("answer");
			if(restring==null) return list;
			String tem[]=restring.split(" ");
			
			for(int i=0;i<tem.length;i+=2)
				{
					rl=new Relation();
					rl.setEntityName(tem[i]);
					if(i+1<tem.length) rl.setProfileid(tem[i+1]);
					rl.setId(rs.getInt("id"));
					list.add(rl);
				}
			rs.close();
			conn.close();
			DAO.closeSession();
			
		}
		catch(Exception ee)
		{
			MyLogger.Logger("Exception when get Entity ", null,"error");
			ee.printStackTrace();
		}
		return list;
	}
//根据 NE跟docuemmntid属性 获取联系  用到 最新动态用到了
	public String getRelation(String documentId,String nename) throws Exception 
	{
		Session session=DAO.openSession();
		String  sql="select id 'id',answer 'answer'" +
		" from answer as a  where  a.documentid like '%"+documentId+"%' and "+ 
							" (a.nename ='"+nename+"' or a.alias = '"+nename+"')";
	
		Connection conn=session.connection();
		try
		{
			 conn=session.connection();
			ResultSet rs=conn.createStatement().executeQuery(sql);
			conn.commit();
			
			if(!rs.next()) return "暂无数据";
			String restring=rs.getString("answer");
			
			if(restring==null||"".equals(restring)) return "暂无资源";
			ReadXMLFromString rxfs=new ReadXMLFromString();
			rxfs.setSource(restring);
			
			return rxfs.readerValueByBode(false,rs.getInt("id"));
		
		}
		catch(Exception ee)
		{
			MyLogger.Logger("Exception when get Entity ", null,"error");
			ee.printStackTrace();
			return "暂无资源";
		}
		finally
		{
			conn.close();
			DAO.closeSession();
		}

	
	}
	//根据 用到 文本用到了
	public String getRelatonText(String id,String nename)
	{
		String text="";
		try {
			nename=java.net.URLDecoder.decode(nename,"utf-8");
		} catch (UnsupportedEncodingException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		
		Session session=DAO.openSession();
		String  sql="select id 'id',extractValue(a.answer,\"/profile//NE[@name='"+nename+"']/text\") 'answer'" +
				" from answer as a where a.id='"+id+"'";
		//System.out.println(sql);
		try
		{
			
			Connection conn=session.connection();
			ResultSet rs=conn.createStatement().executeQuery(sql);
			conn.commit();
			if(!rs.next()) return text=new String("暂无此资源".getBytes("utf-8"),"utf-8");
			String result=rs.getString("answer");
			if(result!=null&&!result.equals(""))
			{
			String tem[]=result.split(" ");
			if(tem.length>0) text=tem[0];
			}
			else
				text=new String("暂无此资源".getBytes("utf-8"),"utf-8");
			
			
		}
		catch(Exception ee)
		{
			ee.printStackTrace();
			try {
				text=new String("暂无此资源".getBytes("utf-8"),"utf-8");
			} catch (UnsupportedEncodingException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		return text;
	}
	/*修改了这个程序  documentid没查询*/
public String getProfile(String documentId,String ne,String enname) throws Exception 
	{
		String result="";
		Session session=null;
		Connection con=null;
		try
		{
			session=DAO.openSession();
			con=session.connection();
			con.setAutoCommit(false);
			String sql="select a.answer 'answer' from" +
					" answer as a where a.nename='"+enname+"'";
							
			ResultSet rs=con.createStatement().executeQuery(sql);
			con.commit();
			if(rs.next())
			result=rs.getString("answer");
			else result="暂无";
			
		}
		catch(Exception ee)
		{
			
			ee.printStackTrace();
			con.rollback();
		}
		finally
		{
			con.close();
			DAO.closeSession();
		}
	
		return result;
	}
	public  void addQuestionToDB(List<String> list,String question)
	{
		Session session=DAO.openSession();
		Transaction ts=session.beginTransaction();
		try
		{
			if(list!=null&&list.size()>0)
			{

				Question que=new Question();
				que.setQuestion(question);
				que.setQdatetime(new Date());
				for(int i=0;i<list.size();i+=2)
				{
					Qanswer answer=new Qanswer();
					answer.setAnswer(list.get(i));
					answer.setQuestion(que);
					que.addAnswertoquestion(answer);
					session.save(answer);
				
				}
			
			}
			ts.commit();
		}
		catch(Exception ee)
		{
			ts.rollback();
			ee.printStackTrace();
		}
		finally
		{
			DAO.closeSession();
		}
	}
	public Question getQuestionById(Integer id)
	{
		Session session =DAO.openSession();
		Question question=null;
		try
		{
			question=(Question)session.get(Question.class, id);
		}
		catch(Exception ee)
		{
			ee.printStackTrace();
			
		}
		finally
		{
			DAO.closeSession();
		}
		return question;
	}
	public List<Question> getAllUserQuest(int size,int pg,int type)
	{
		List<Question> list=null;
		Query query=null;
		Session session=DAO.openSession();
		Transaction ts=session.beginTransaction();
		String sql="";
		if(type==0)
		{
			sql="from Question as a where a.answer is null";
		}
		else sql="from Question as a where a.answer is not null";
		try
		{
		query=session.createQuery(sql);
		query.setFirstResult((pg-1)*size);
		query.setMaxResults(size);
		list=(List<Question>)query.list();
		ts.commit();
		}
		catch(Exception ee)
		{
			ee.printStackTrace();
			ts.rollback();
			
		}
		return list;
	}
	public String deleteQuestion(Integer id)
	{
		Session session=DAO.openSession();
		Transaction ts=session.beginTransaction();
		try
		{
			session.delete(session.load(Question.class, id));
			ts.commit();
		}
		catch(Exception ee)
		{
			ts.rollback();
			ee.printStackTrace();
		
			return "error";
		}
		finally
		{
			DAO.closeSession();
		}
		return "success";
	}
	public String SetQuestionTODB(Integer qid,Integer aid)
	{
		Session session=DAO.openSession();
		Transaction ts=session.beginTransaction();
		try
		{
			Question question=(Question)session.load(Question.class, qid);
			Iterator<Qanswer> it=question.getQanswers().iterator();
			while(it.hasNext())
			{
				Qanswer an=it.next();
				if(an.getId().equals(aid))
				{
					question.setAnswer(an);
				}
				else 
				{
					session.delete(an);
				}
			}
			ts.commit();
		}
		catch(Exception ee)
		{
			ts.rollback();
			return "error";
		}
		return "success";
	}

	//根据profile NE 获取联系
	/*
	public String getYiqiRelation(String name) throws Exception
	{
	
		//String sql="select answer as answer from answer where answer like '%<profile NE = \""+name+"\" %'";
	//	System.out.println(sql);
	//	ResultSet rs=con.createStatement().executeQuery(sql);
		List<Relation> list=new ArrayList<Relation>();
		Session session=DAO.openSession();
		String  sql="select id 'id',extractValue(a.answer,'/profile//CE/@name|/profile/CE/NE/@name|/profile//CE/NE/documentID') 'answer'" +
		" from answer as a  where a.answer like '%\""+name+"\"%'";
		StringBuffer sb=new StringBuffer();
		try
		{
			
			//System.out.println(sql);
			//MyLogger.Logger(sql, null, "info");
			Connection conn=session.connection();
			ResultSet rs=conn.createStatement().executeQuery(sql);
			conn.commit();
			
			if(!rs.next()) return "暂无数据";
			String restring=rs.getString("answer");
			
			if(restring==null||"".equals(restring)) return "暂无数据";
			String tem[]=restring.split(" ");
			String id=Integer.valueOf(rs.getInt("id")).toString();
			//sb.append("[");
			
			for(int i=0;i<tem.length;)
			{
				if(CEMap.isContainkey(tem[i]))
				{
					if(i!=0)
					{
						sb.append("</fieldset>");
					}
					if(i+1<tem.length)
					{
						
						if(!CEMap.isContainkey(tem[i+1]))
						{
							sb.append("<fieldset><legend>"+CEMap.getValueByKey(tem[i].toLowerCase()));
							sb.append("</legend><div><span id='"+id+"'value='"+tem[i+2]+"'>");
							sb.append(tem[i+1]);
							sb.append("</span></div>");
							i+=3;
						}
						else
						{
							i++;
						}
					}
					else break;
				}
				else
				{
					sb.append("<div><span value='"+tem[i+1]+"'>");
					sb.append(tem[i]);
					sb.append("</span></div>");
					i+=2;
				}
			}
			
			
			
			rs.close();
			conn.close();
			DAO.closeSession();
		
		}
		catch(Exception ee)
		{
			MyLogger.Logger("Exception when get Entity ", null,"error");
			ee.printStackTrace();
		}
	return sb.toString();
	}

	*/
	/*用到了*/
	public String getYiqiRelation(String name,boolean isyiqi) throws Exception
	{
		Session session=DAO.openSession();
		String sql="select id,answer as answer from answer where nename ='"+name+"' " +
				"or alias ='"+name+"'";
		ReadXMLFromString rxfs=new ReadXMLFromString();
		int id = 0;
		Connection conn=session.connection();
		try
		{
		ResultSet rs=conn.createStatement().executeQuery(sql);
		conn.commit();
		
		if(!rs.next()) return "暂无数据";
		String restring=rs.getString("answer");
		if(restring==null||"".equals(restring)) return "暂无数据";
	
	
		rxfs.setSource(restring);id=rs.getInt("id");
		}catch(Exception ee)
		{
			ee.printStackTrace();
		}
		finally
		{
			conn.close();
		DAO.closeSession();
		}
		return rxfs.readerValueByBode(isyiqi,id);
		
		
	}
}
